<?php
/**
* Classe permettant de manipuler les bases de données
* Classe utilisant le design patern singleton 
* Accés depuis l'exterieur via la methode statique database::getInstance()
* 
*/
class DatabaseMateriel {

	private static $connection = null;

	/**
	* @desc : Constructeur, initialise la connection
	* @return void
	*/
	private function __construct()
	{
		try {
			// On instancie PDO et on se connecte a la base de données
			self::$connection = new PDO('mysql:host=localhost;dbname=m2l', 'root', '',  array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
		} catch(PDOException $e) {
			echo "Erreur de connection à la base de données : <b>".$e->getMessage()."</b>";
		}
	}

	/**
	* @desc : Methode statique d'accès à la database, retourne une instance de la connection si elle existe, en créer une si aucune existante
	* @return instance de database
	*/
	public static function getInstance() {
		if(self::$connection === null) {
			return new self();
		}
		return self::$connection;
	}

	
	// -------------------------------------------------------
	// *** Début des methodes en rapport avec le materiel ***
	// -------------------------------------------------------
	public function addMateriel($description, $reference, $tailleLot, $idEtat, $idMaterielSport) {
		
		$query = self::$connection->prepare("INSERT INTO materiel(description, reference, etat_reservation, taille_lot, etat_materiel_id_etat, materiel_sport_id_materiel_sport) 
											VALUES(:description, :reference, :etatReservation, :tailleLot, :idEtat, :idMaterielSport)
											");
		$query->bindValue(":description", $description, PDO::PARAM_STR);
		$query->bindValue(":reference", $reference, PDO::PARAM_STR);
		$query->bindValue(":tailleLot", $tailleLot, PDO::PARAM_INT);
		$query->bindValue(":etatReservation", false, PDO::PARAM_BOOL);
		$query->bindValue(":idEtat", $idEtat, PDO::PARAM_INT);
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		} 

		return false;
	}

	public function updateMateriel($idMateriel, $idEtat, $idMaterielSport, $description, $tailleLot, $reference) {

		$currentTailleLot = $this->getCurrentTailleLot($idMateriel);

		if($tailleLot < $currentTailleLot) {
			$this->updateStockMaterielSport($idMaterielSport, $tailleLot, "delete");
		}

		if($tailleLot > $currentTailleLot) {
			$this->updateStockMaterielSport($idMaterielSport, $tailleLot,"add");
		}
		
		$query = self::$connection->prepare("UPDATE materiel SET description = :description, reference = :reference, taille_lot = :tailleLot, etat_materiel_id_etat = :idEtat, materiel_sport_id_materiel_sport = :idMaterielSport WHERE idMateriel = :idMateriel");
		$query->bindValue(":description", $description, PDO::PARAM_STR);
		$query->bindValue(":reference", $reference, PDO::PARAM_STR);
		$query->bindValue(":tailleLot", $tailleLot, PDO::PARAM_INT);
		$query->bindValue(":idEtat", $idEtat, PDO::PARAM_INT);
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		} 

		return false;
	}
/*

	public function deleteMateriel($idMateriel) {
		
		$query = self::$connection->prepare("DELETE FROM materiel WHERE stock = :idMateriel");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}

		return false;
	}
	*/

	public function updateStockMaterielSport($idMaterielSport, $tailleLot, $action="add") {
		$query = self::$connection->prepare("UPDATE materiel_sport SET stock = :stock WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		switch($action) {
			case "add":
				$query->bindValue(":stock", $this->getStock($idMaterielSport)->stock+$tailleLot, PDO::PARAM_INT);
				break;
			case "delete":
				$query->bindValue(":stock", $this->getStock($idMaterielSport)->stock-$tailleLot, PDO::PARAM_INT);
				break;
		}
		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}
		return false;
	}
	
	public function getListe($idMaterielSport, $idAssociation) {
		$query = self::$connection->prepare("SELECT id_materiel, description FROM materiel, materiel_sport WHERE materiel_sport_id_materiel_sport = :idMaterielSport AND association_numero_association = :idAssociation");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);
		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);

		$query->execute();
		return $query->fetchAll(PDO::FETCH_OBJ);
	}

	public function getAllEtat() {
		$query = self::$connection->prepare("SELECT id_etat, type_etat FROM etat_materiel");
		$query->execute();
		return $query->fetchAll(PDO::FETCH_OBJ);
	}

	public function getIdMaterielSport($idMateriel) {
		$query = self::$connection->prepare("SELECT materiel_sport_id_materiel_sport FROM materiel WHERE id_materiel = :idMateriel");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function getCurrentTailleLot($idMateriel) {
		$query = self::$connection->prepare("SELECT taille_lot FROM materiel WHERE id_materiel = :idMateriel");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function getStock($idMaterielSport) {

		$query = self::$connection->prepare("SELECT stock FROM materiel_sport WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function addMaterielSport($designation, $idAssociation) {
		$query = self::$connection->prepare("INSERT INTO materiel_sport(designation_materiel_sport, association_numero_association)  VALUES(:designation, :idAssociation)");
		$query->bindValue(":designation", $designation, PDO::PARAM_STR);
		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}
		return false;
	}
	
	public function updateMaterielSport($idMaterielSport, $designation) {
		$query = self::$connection->prepare("UPDATE materiel_sport SET designation_materiel_sport = :designation WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue("designation", $designation, PDO::PARAM_STR);
		$query->bindValue("idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}
		return false;
	}

	public function deleteMaterielSport($idMaterielSport) {
		$query = self::$connection->prepare("DELETE FROM materiel_sport WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}
		return false;
	}

	public function deleteMaterielFromMaterielSport($idMaterielSport) { 
		$query = self::$connection->prepare("DELETE FROM materiel WHERE materiel_sport_id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		if($query->execute()) {
			return true;
		}
		return false;
	}

	public function getInfoMaterielSport($idMaterielSport) {
		$query = self::$connection->prepare("SELECT id_materiel_sport, designation_materiel_sport FROM materiel_sport");

		$query->execute();

		return $query->fetch(PDO::FETCH_ASSOC);
	}

	public function getAllMaterielSportByAssoc($idAssociation) {
		$query = self::$connection->prepare("SELECT id_materiel_sport, designation_materiel_sport FROM materiel_sport WHERE association_numero_association = :idAssociation");
		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);
		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}

}
?>